import pymysql
from config.db_config import DB_CONFIG  

class DBConnection:
    """数据库连接类"""
    _connection = None

    @classmethod	# 单实例，保证只创建一个数据库连接，且不需要创建类的实例就能获取连接
    def get_connection(cls):
        """获取数据库连接"""
        if cls._connection is None:
            cls._connection = pymysql.connect(**DB_CONFIG)	# 解包运算符,将字典 DB_CONFIG 展开成关键字参数
        return cls._connection

    @classmethod
    def close(cls):
        """关闭数据库连接"""
        if cls._connection:
            cls._connection.close()
            cls._connection = None
            
    @classmethod
    def init_database(cls):
        """初始化数据库"""
        try:
            conn = cls.get_connection()
            cursor = conn.cursor()
            
            # 创建用户表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS user (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    username VARCHAR(50) NOT NULL,
                    password VARCHAR(50) NOT NULL,
                    telephone VARCHAR(11) NOT NULL UNIQUE,
                    gender VARCHAR(10) DEFAULT '未设置',
                    age INT DEFAULT 0,
                    role INT(1) NOT NULL DEFAULT 1 COMMENT '0:管理员, 1:普通用户',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                )
            """)
            # 创建商品表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS product (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    product_name VARCHAR(100) NOT NULL,
                    price DECIMAL(10,2) NOT NULL,
                    sales INT DEFAULT 0
                )
            """)
            # 创建订单表
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS `order` (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    product_name VARCHAR(100) NOT NULL,
                    price DECIMAL(10,2) NOT NULL,
                    customer_phone VARCHAR(11) NOT NULL,
                    customer_name VARCHAR(50) NOT NULL,
                    delivery_status INT(1) NOT NULL DEFAULT 0 COMMENT '0:未派发, 1:正在派发, 2:完成派发',
                    order_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    expected_delivery_time TIMESTAMP NULL
                )
            """)
            
            # 检查是否已存在管理员账户
            cursor.execute("SELECT COUNT(*) FROM user WHERE role = 0")
            admin_count = cursor.fetchone()[0]
            
            # 如果没有管理员，创建默认管理员账户
            if admin_count == 0:
                cursor.execute("""
                    INSERT INTO user (username, password, telephone, gender, age, role) 
                    VALUES ('管理员', 'root123', '15281156812', '未设置', 0, 0)
                """)
                
                # 获取刚插入的管理员ID
                admin_id = cursor.lastrowid
                print(f"已创建默认管理员账户：")
                print(f"  用户ID: {admin_id}")
                print(f"  用户名: 管理员")
                print(f"  手机号: 15281156812")
                print(f"  密码: root123")
                print(f"  性别: 未设置")
                print(f"  年龄: 0（未知）")
                print(f"  角色: 管理员")
            
            conn.commit()
            print("数据库初始化成功! ")
            return True
        
        except pymysql.Error as e:
            print(f"初始化数据库失败: {e}")
            conn.rollback()
            return False
        finally:
            cursor.close()

    @classmethod
    def test_connection(cls):
        """测试数据库连接函数"""
        try:
            # 尝试获取连接并创建游标
            conn = cls.get_connection()
            cursor = conn.cursor()
            
            # 执行简单查询测试连接
            cursor.execute("SELECT VERSION()")
            version = cursor.fetchone()
            print("数据库连接成功！")
            print(f"MySQL版本: {version[0]}")
            
            # 获取数据库信息
            cursor.execute("SHOW TABLES")
            tables = cursor.fetchall()
            print(f"数据库中的表: {tables if tables else '暂无表'}")
            if tables:
                print("数据库已初始化，无需重新创建表")
                return False
            return True
            
        except pymysql.Error as e:
            print(f"连接失败: {e}")
            return False
        finally:
            cursor.close()

if __name__ == "__main__":
    # 如果直接运行此文件，则测试连接
    if DBConnection.test_connection():
        DBConnection.init_database()